// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

namespace SnapObjects.Data
{
    /// <summary>
    ///     Represents an object used to build the WHERE clause in a SQL statement. 
    ///     The WHERE clause specifies the search condition for the rows returned by a query.
    /// </summary>
    public interface ISqlWhereBuilder : ISqlGroupBuilder
    {
        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL 
        ///     expression on the left of the operator and a specific value on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereValue(string left, object value);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on 
        ///     the left of the operator and a specific value on the right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereValue(string left, SqlBinaryOperator sqlOperator, object value);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression 
        ///     on the left of the operator and a SQL parameter on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter on the right of the operator.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(string left, ISqlParameter parameter);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression 
        ///     on the left of the operator and a SQL parameter on the right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter on the right of the operator.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(string left, string right);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(string left, SqlBinaryOperator sqlOperator, string right);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left 
        ///     of the operator and a SQL subquery on the right. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(string left, ISqlBuilder sqlBuilder);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on
        ///     the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause by an ISqlWhereCondition object.
        /// </summary>
        /// <param name="condition">
        ///     An ISqlWhereCondition which represents a search condition in the WHERE clause. 
        ///     This object can be created by using the SqlBuilder.Where method and so on.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(ISqlWhereCondition condition);

        /// <summary>
        ///     Creates a WHERE clause, adds a search condition to the WHERE clause by an ISqlWhereCondition object, 
        ///     and specifies a name for the search condition.
        /// </summary>
        /// <param name="condition">
        ///     An ISqlWhereCondition which represents a search condition in the WHERE clause. 
        ///     This object can be created by using the SqlBuilder.Where method and so on.
        /// </param>
        /// <param name="whereName">
        ///     The name for the search condition. It can be used to remove a search condition from the WHERE clause.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr Where(ISqlWhereCondition condition, string whereName);

        /// <summary>
        ///     Creates a WHERE clause, and adds a raw SQL to the WHERE clause.
        /// </summary>
        /// <param name="whereClause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereRaw(string whereClause, params ISqlParameter[] parameters);

        /// <summary>
        ///     Creates a WHERE clause, adds a raw SQL to the WHERE clause, and specifies a name for the raw SQL.
        /// </summary>
        /// <param name="whereClause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="whereName">The name for the raw SQL. It can be used to remove the raw SQL from the WHERE clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereRaw(string whereClause, string whereName, params ISqlParameter[] parameters);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Uses IS NULL operator to search for values that are null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereIsNull(string left);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Uses IS NOT NULL operator to search for values that are not null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NOT NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereIsNotNull(string left);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses EXISTS to test for the 
        ///     existence of the record returned from a subquery.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the EXISTS operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereExists(ISqlBuilder sqlBuilder);

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses the NOT EXISTS 
        ///     operator to test if the record returned from a subquery does not exist.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the NOT EXISTS operator.
        /// </param>
        /// <returns>s
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        ISqlWhereAndOr WhereNotExists(ISqlBuilder sqlBuilder);

        /// <summary>
        ///     Determines whether any search condition has been added to the WHERE clause of the current 
        ///     object using a Where or WhereRaw method and so on.
        /// </summary>
        bool HasWhere { get; }

        /// <summary>
        ///     Removes all of the search conditions that have been added to the WHERE clause.
        /// </summary>
        /// <returns>
        ///     Returns true if the removal is successful, and returns false if there were no search conditions in the WHERE clause.
        /// </returns>
        bool RemoveWhere();

        /// <summary>
        ///     Removes the search condition (specified by name) from the WHERE clause.
        /// </summary>
        /// <param name="whereName">The name for one or more search conditions in the WHERE clause.</param>
        /// <returns>
        ///     Returns true if the removal is successful, and returns false if the specified search
        ///     condition name does not exist in the WHERE clause.
        /// </returns>
        bool RemoveWhere(string whereName);

        /// <summary>
        ///     Returns an ISqlGroupBuilder object which represents an object used to build the GROUP BY clause in a SQL statement.
        /// </summary>
        /// <returns>
        ///     Returns the ISqlGroupBuilder object which represents an object used to build the GROUP BY clause in a SQL statement.
        /// </returns>
        ISqlGroupBuilder AsGroupBy();
    }
}
